<?php

App::uses('Crud', 'Model');
App::uses('Test', 'Model');
App::uses('UserArea', 'Model');
App::uses('Area', 'Model');
App::uses('Room', 'Model');
App::uses('User', 'Model');
App::uses('Schedule', 'Model');
App::uses('Evaluation', 'Model');
App::uses('Attempt', 'Model');

class UserTest extends Crud {
    const STATUS_ENABLED = 1;
    const STATUS_DISABLED = 0;

    public function getEnabledByUser($userId) {
        $filters = array(
            'fields' => array(
                'UserTest.id as value',
                't.name as text',
                's.start as start',
                'r.name as room'
            ),
            'joins' => array(
                array(
                    'table' => 'schedules',
                    'type' => 'INNER',
                    'alias' => 's',
                    'conditions' => array(
                        's.id = UserTest.schedule_id',
                        's.closed' => Schedule::NO_CLOSED,
                        's.status' => Schedule::STATUS_ENABLED,
                    )
                ),
                array(
                    'table' => 'tests',
                    'type' => 'INNER',
                    'alias' => 't',
                    'conditions' => array(
                        't.id = s.test_id',
                        't.status' => Test::STATUS_ENABLED,
                    )
                ),
                array(
                    'table' => 'rooms',
                    'type' => 'INNER',
                    'alias' => 'r',
                    'conditions' => array(
                        'r.id = s.room_id',
                        'r.status' => Room::STATUS_ENABLED
                    )
                )
            ),
            'conditions' => array(
                's.start >= CURDATE()',
                'UserTest.user_id' => $userId,
                'UserTest.status' => self::STATUS_ENABLED
            )
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getEnabledByTest($scheduleId) {
        $filters = array(
            'fields' => array(
                'UserTest.id as value',
                'UserTest.user_id as userId',
                'u.full_name as text',
                'u.nomina as nomina',
                'UserTest.registration_date as reg_date',
                'UserTest.qualification as calif',
                'UserTest.qualification as band_calif',
                'IF ( e.attempts_available IS NULL, t.attempts, e.attempts_available) as att_ab',
                'IF ( a.qualification IS NULL, NULL, MAX(a.qualification)) as max_calif',
                'IF ( a.application_date IS NULL, NULL, MAX(a.application_date)) as date_ap'
            ),
            'joins' => array(
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'type' => 'INNER',
                    'alias' => 'u',
                    'conditions' => array(
                        'u.id = UserTest.user_id',
                        'u.status' => User::STATUS_ENABLED,
                    )
                ),
                array(
                    'table' => 'schedules',
                    'type' => 'INNER',
                    'alias' => 's',
                    'conditions' => array(
                        's.id = UserTest.schedule_id',
                        's.status' => Schedule::STATUS_ENABLED,
                    )
                ),
                array(
                    'table' => 'tests',
                    'type' => 'INNER',
                    'alias' => 't',
                    'conditions' => array(
                        't.id = s.test_id',
                        't.status' => Test::STATUS_ENABLED,
                    )
                ),
                array(
                    'table' => 'evaluations',
                    'type' => 'LEFT',
                    'alias' => 'e',
                    'conditions' => array(
                        'e.user_id = UserTest.user_id',
                        'e.test_id = s.test_id',
                        'e.status' => Evaluation::STATUS_ENABLED,
                    )
                ),
                array(
                    'table' => 'attempts',
                    'type' => 'LEFT',
                    'alias' => 'a',
                    'conditions' => array(
                        'a.evaluation_id = e.id',
                        'e.status' => Attempt::STATUS_ENABLED,
                    )
                )
            ),
            'order' => array('reg_date ASC'),
            'group' => array('value'),
            'conditions' => array(
                'UserTest.schedule_id' => $scheduleId,
                'UserTest.status' => self::STATUS_ENABLED
            )
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getEnabledById($userTestId) {
        $filters = array(
            'fields' => array(
                'UserTest.id as value',
                'UserTest.user_id as userId',
                'u.full_name as text',
                'u.nomina as nomina',
                'UserTest.registration_date as reg_date',
                'UserTest.qualification as calif',
                'UserTest.qualification as band_calif',
                'IF ( e.attempts_available IS NULL, t.attempts, e.attempts_available) as att_ab',
                'IF ( a.qualification IS NULL, NULL, MAX(a.qualification)) as max_calif',
                'IF ( a.application_date IS NULL, NULL, MAX(a.application_date)) as date_ap'
            ),
            'joins' => array(
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'type' => 'INNER',
                    'alias' => 'u',
                    'conditions' => array(
                        'u.id = UserTest.user_id',
                        'u.status' => User::STATUS_ENABLED,
                    )
                ),
                array(
                    'table' => 'schedules',
                    'type' => 'INNER',
                    'alias' => 's',
                    'conditions' => array(
                        's.id = UserTest.schedule_id',
                        's.status' => Schedule::STATUS_ENABLED,
                    )
                ),
                array(
                    'table' => 'tests',
                    'type' => 'INNER',
                    'alias' => 't',
                    'conditions' => array(
                        't.id = s.test_id',
                        't.status' => Test::STATUS_ENABLED,
                    )
                ),
                array(
                    'table' => 'evaluations',
                    'type' => 'LEFT',
                    'alias' => 'e',
                    'conditions' => array(
                        'e.user_id = UserTest.user_id',
                        'e.test_id = s.test_id',
                        'e.status' => Evaluation::STATUS_ENABLED,
                    )
                ),
                array(
                    'table' => 'attempts',
                    'type' => 'LEFT',
                    'alias' => 'a',
                    'conditions' => array(
                        'a.evaluation_id = e.id',
                        'e.status' => Attempt::STATUS_ENABLED,
                    )
                )
            ),
            'conditions' => array(
                'UserTest.id' => $userTestId,
                'UserTest.status' => self::STATUS_ENABLED
            )
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getUsIdEnabledByTest($scheduleId) {
        $filters = array(
            'fields' => array(
                'UserTest.user_id as userId',
            ),
            'conditions' => array(
                'UserTest.schedule_id' => $scheduleId,
                'UserTest.status' => self::STATUS_ENABLED
            )
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getUsCountEnabledByTest($scheduleId) {
        $filters = array(
            'fields' => array(
                'COUNT(UserTest.id) as cant',
            ),
            'conditions' => array(
                'UserTest.schedule_id' => $scheduleId,
                'UserTest.status' => self::STATUS_ENABLED
            )
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getUsCountCalifEnabledByTest($scheduleId) {
        $filters = array(
            'fields' => array(
                'COUNT(UserTest.id) as cant',
            ),
            'conditions' => array(
                'UserTest.schedule_id' => $scheduleId,
                'UserTest.qualification IS NOT NULL',
                'UserTest.status' => self::STATUS_ENABLED
            )
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getTestsForKardex($userId) {
        $filters = array(
            'fields' => array(
                't.name as course',
                's.start as date',
                't.id as test'
            ),
            'joins' => array(
                array(
                    'type' => 'INNER',
                    'table' => 'schedules',
                    'alias' => 's',
                    'conditions' => array(
                        's.id = UserTest.schedule_id',
                        's.status' => Schedule::STATUS_ENABLED,
                    )
                ),
                array(
                    'type' => 'INNER',
                    'table' => 'tests',
                    'alias' => 't',
                    'conditions' => array(
                        't.id = s.test_id',
                        't.status' => Test::STATUS_ENABLED,
                    )
                )
            ),
            'conditions' => array(
                'UserTest.qualification IS NULL',
                'UserTest.user_id' => $userId,
                'UserTest.status' => self::STATUS_ENABLED,
            ),
            'order' => array(
                'date ASC'
            ),
        );
        $record = $this->flatArray($this->find('all', $filters));
        return $record;
    }
}